﻿SELECT Z.AREA_MGR, CAST(NULL AS VARCHAR(50)) ACCT_MGR, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT
FROM (
SELECT B.AREA_MGR, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT AREA_MGR FROM MIS.ACC_BASE_DATA WHERE AREA_MGR GT'' GROUP BY 1) B) Z
LEFT OUTER JOIN (
SELECT B.AREA_MGR, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 TOT_O-(ALTCHNL_O+COR_O+CS_O+D2D_O+MOBILITYDMD_O+NATIONALRET_O+OBTM_O+ONLINE_O+P2RCALLCEN_O+STORES_O+TE_O+WEB_O) OTH_O,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O
FROM MIS.ACC_RAW A
 INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID,
 (SELECT MIN(CALENDAR_DATE) BOM, MAX(CALENDAR_DATE) EOM
  FROM SYS_CALENDAR.CALENDAR
  WHERE MONTH_OF_CALENDAR=(
  SELECT MONTH_OF_CALENDAR - ?OFFSET FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)) C
WHERE A.FEATURE NOT IN ('000','BVSTAN')
AND A.START_DT BETWEEN C.BOM AND C.EOM
GROUP BY 1,2,3) Y ON Z.AREA_MGR=Y.AREA_MGR AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
UNION ALL
SELECT Z.AREA_MGR, Z.ACCT_MGR, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT
FROM (
SELECT B.AREA_MGR, B.ACCT_MGR, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT AREA_MGR, ACCT_MGR FROM MIS.ACC_BASE_DATA WHERE ACCT_MGR GT '' GROUP BY 1,2) B) Z
LEFT OUTER JOIN (
SELECT B.ACCT_MGR, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 TOT_O-(ALTCHNL_O+COR_O+CS_O+D2D_O+MOBILITYDMD_O+NATIONALRET_O+OBTM_O+ONLINE_O+P2RCALLCEN_O+STORES_O+TE_O+WEB_O) OTH_O,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O
FROM MIS.ACC_RAW A
 INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID,
 (SELECT MIN(CALENDAR_DATE) BOM, MAX(CALENDAR_DATE) EOM
  FROM SYS_CALENDAR.CALENDAR
  WHERE MONTH_OF_CALENDAR=(
  SELECT MONTH_OF_CALENDAR - ?OFFSET1 FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)) C
WHERE A.FEATURE NOT IN ('000','BVSTAN')
AND A.START_DT BETWEEN C.BOM AND C.EOM
GROUP BY 1,2,3) Y ON Z.ACCT_MGR=Y.ACCT_MGR AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
ORDER BY 1,2,3,4;